If statements always showing true instead of value

vgersghost

Vgersghost
Local time
Today, 03:53
Joined
Apr 29, 2005
Messages
106
I been trying to nest if statments and vlookups to find values from various coulums with no luck. See attached spreadsheet. See sheet Prod cell H2 to give you an idea. The data is on the shipschd sheet, which is downloaded from an As400 system.

Any idea's or help!

Thanks dmh
 

Attachments

Howdy. What exactly do you expect in cell H2?
________
LF
 
Last edited:
Column H2 informtion

I need a value in H2 based on a vlookup for columns C (value being look up) and D (value needed) from sheet shipschd, if not found there needs to look at columns F and G and use the G column value, and so forth.

Thank you
 
I understand. But in this specific instance what value do you expect in H2 instead of TRUE?
________
Buy Vapir One
 
Last edited:
Value

I need the number to show up in H2 from column EQY01 (400) or the column EQY02 (200) and so forth in sheet shipschd.

thank you
 
Sorry for being so old and dense, but how are you determining whether it is EQY01 or EQY02 etc.? You have to have some specific instance for the formula to return. So at the simplest step, what is your criteria for EQY01, so that we can get a specific value in H2 (building the easiest formula first). Unless we have a starting point, there doesn't seem to be any way of solving your problem.
________
vapir oxygen vaporizer
 
Last edited:
vlookup in h2

Look at the formula in H2 sheet prod. It has vlookup to find the data in eqy01. Based on looking up $C4&$H$3 on sheet prod.
If iI cannot find it in $C$4:$D$41 then look in $F$4:$G$41 if not there then $I$4:$J$39, and so forth.


H2 Formula:
=IF(ISNA(VLOOKUP($C4&$H$3,shipschd!$C$4:$D$41,2,FALSE)),
ISNA(VLOOKUP($C4&$H$3,shipschd!$F$4:$G$41,2,FALSE)),
IF(ISNA(VLOOKUP($C4&$H$3,shipschd!$F$4:$G$39,2,FALSE)),
ISNA(VLOOKUP($C4&$H$3,shipschd!$F$4:$G$39,2,FALSE)),
IF(ISNA(VLOOKUP($C4&$H$3,shipschd!$I$4:$J$39,2,FALSE)),
(VLOOKUP($C4&$H$3,shipschd!$I$4:$J$39,2,FALSE)))))

Hope this helps. Thank you again
 
Okay, your formula above does not match what you have in the example workbook.

Try this in H2, based on the sample you provided. Your problem was that the IF had no means of "escape" to the correct answer. I reversed it and it seems to work. Note that I separated the individual lookups into cells H4:H9, then combined the IF formula in H12, then substituted those individual formulas into H11.

=IF(NOT(ISNA(VLOOKUP($C2&$H$1,shipschd!$C$2:$D$40,2,FALSE))),VLOOKUP($C2&$H$1,shipschd!$C$2:$D$40,2,FALSE),IF(NOT(ISNA(VLOOKUP($C2&$H$1,shipschd!$F$2:$G$40,2,FALSE))),VLOOKUP($C2&$H$1,shipschd!$F$2:$G$40,2,FALSE),IF(NOT(ISNA(VLOOKUP($C2&$H$1,shipschd!$F$2:$G$38,2,FALSE))),VLOOKUP($C2&$H$1,shipschd!$F$2:$G$38,2,FALSE),IF(NOT(ISNA(VLOOKUP($C2&$H$1,shipschd!$F$2:$G$38,FALSE))),VLOOKUP($C2&$H$1,shipschd!$F$2:$G$38,FALSE),IF(NOT(ISNA(VLOOKUP($C2&$H$1,shipschd!$I$2:$J$38,2,FALSE))),VLOOKUP($C2&$H$1,shipschd!$I$2:$J$38,2,FALSE),VLOOKUP($C2&$H$1,shipschd!$I$2:$J$38,2,FALSE))))))
________
AEROSTAR
 

Attachments

Last edited:
Thank you

Thank you, It seems to work great. Last request (well maybe). I tried putting an additonal if statement if front with ISNA to make a zero appear instead of #N/A or #value, if no value was found.

Well no luck solving that problem, so I need your help again.

I learned alot just looking and reading what you placed in the formula. Also liked the way you broke down each formula and placed them in the last formula. I would have never thought about that option.

thank you
 
If you are trying to use the entire expression with another IF(ISNA combination, you have exceeded the limits of the formula in a cell, 1,024 characters. You already have 587 characters as it is (in my formula). If you double it, then you have 1,174 characters.

Given the increasing complexity, can you set up a couple of intermediate formulae? In other words, in my example, set up an combo for cells H4, H5, H6 in cell H15:

=IF(NOT(ISNA(H4)),H4,IF(NOT(ISNA(H5)),H5,IF(NOT(ISNA(H6)),H6,)))

and another one for H7, H8, H9 in H16

=IF(NOT(ISNA(H7)),H7,IF(NOT(ISNA(H8)),H8,H9))

Then have in H17 with your final check.
________
STOBART VK M-SPORT FORD RALLY TEAM PICTURE
 
Last edited:
Thanks for idea and all your help

I will have to rework allot in my spreadsheet to make room for all the formulas, (there’s about 1000, rows down to look up) but you given me a great start. I think breaking them down might be the best way, because it will let me expand my number of columns I can lookup. I really appreciate that.

Thank you
 
Great. Also, by breaking into separate formulas it will be much easier to trouble-shoot. If you are redesigning, you might want to also include a worksheet (that can be later hidden) that explains the process of the formulas and the logic behind them. Also, you helping formulas can be placed in a separate columns (perhaps on the far right Columns IA:IV). That way your formulas there and in your main sheet can be copied to the right and down as needed, and those columns with intermediate calculations can then hidden.

Or you can even have your intermediate columns on a spearate worksheet. So the flow owuld be:

Data > Data Setup > Data Display

For major projects I always do this. The advantage is that if you have changes to your Data, or Data Setup or Data Display, you can change one piece without affecting the others. Saves MUCH time after it is setup properly. I had one that involved two more steps than above, with 100 cities, 63 channels, with weekly data for each, and updated weekly. It took two months (along with my normal projects) to set up, but once it was, the entire process: data pulls (8 different workbooks), setup worksheets (700+), and linked pics to PPT - took less than 20 minutes (data pull was 95% of the time), by changing one cell in each setup workbook.
________
Sidevalve engine
 
Last edited:
As a further thought, you might find INDEX/MATCH more versatile than VLOOKUP. Might offer up some simpler options in the long run.
________
Marijuana test
 
Last edited:
Thank you "shades"

Thanks for all your help. Iwill look into seperating all the formulas and data. THat makes great sense. The sheet I'm working on as I stated has 1286 rows down and the lookup sheet when complete will have close to 300 columns of data to lookup. Quite a task. The help you given me to date is priceless.
Thanks again.
 

Users who are viewing this thread

Back
Top Bottom